﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MS.InformationManagement.DAL.DocumentCenter;
using Microsoft.SharePoint;
using support = MS.InformationManagement.Support;
using MS.InformationManagement.Support.Constants;
using MS.InformationManagement.DAL.Support;
using office = Microsoft.Office.DocumentManagement.DocumentSets;
using System.Data;
using MS.InformationManagement.Support;

namespace MS.InformationManagement.Core.Support
{
    public static class RadicadosHelper
    {

        public static SPListItemCollection GetDocumentLinkInExpedienteById(office.DocumentSet expediente, int itemId, string targetList)
        {
            SPQuery queryRecords = new SPQuery();
            queryRecords.Query = string.Format(@"<Where>
                                                  <And>
                                                     <Eq>
                                                        <FieldRef Name='LinkDoc_Target_Id' />
                                                        <Value Type='Number'>{0}</Value>
                                                     </Eq>
                                                     <And>
                                                        <Eq>
                                                           <FieldRef Name='LinkDoc_Target_List' />
                                                           <Value Type='Text'>{1}</Value>
                                                        </Eq>
                                                        <Eq>
                                                           <FieldRef Name='ContentType' />
                                                           <Value Type='Text'>{2}</Value>
                                                        </Eq>
                                                     </And>
                                                  </And>
                                               </Where>", itemId, targetList, GlobalConstants.CTTN_DOCUMENT_LINK);
            queryRecords.Folder = expediente.Folder;
            return expediente.Folder.Item.ParentList.GetItems(queryRecords);
        }

        public static office.DocumentSet getExpedienteById(string expId, string listName)
        {
            string camlQuery = string.Format("<Where><And><Eq><FieldRef Name='{0}' /><Value Type='Text'>{1}</Value></Eq><Eq><FieldRef Name='{2}' /><Value Type='Text'>{3}</Value></Eq></And></Where>", CustomSiteColumns.FIELD_DOCUMENT_EXPEDIENTE_ID, expId, UserLibraryHelper.ContentTypeFieldName, GlobalConstants.CTTN_EXPEDIENTE);
            SPQuery query = new SPQuery() { Query = camlQuery };
            SPListItemCollection result = SPContext.Current.Web.Lists[listName].GetItems(query);
            if (result.Count == 0) return null;
            return Microsoft.Office.DocumentManagement.DocumentSets.DocumentSet.GetDocumentSet(result[0].Folder);
        }

        public static office.DocumentSet GetExpedientById(string expedienteId)
        {
            string camlQuery = string.Format(@"<Where>
                                                  <And>
                                                     <Eq>
                                                        <FieldRef Name='Expediente_Id' />
                                                        <Value Type='Text'>{0}</Value>
                                                     </Eq>
                                                     <Eq>
                                                        <FieldRef Name='ContentType' />
                                                        <Value Type='Text'>{1}</Value>
                                                     </Eq>
                                                  </And>
                                               </Where>", expedienteId, GlobalConstants.CTTN_EXPEDIENTE);
            List<string> viewFields = new List<string>() { "ID", CustomSiteColumns.FIELD_DOCUMENT_EXPEDIENTE_ID };
            List<string> viewListFields = new List<string>() { "Title" };
            SPSiteDataQuery query = QuerySiteHelper.getSiteQueryObject(GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE, viewFields, viewListFields, camlQuery);
            DataTable foundedItems = SPContext.Current.Web.GetSiteData(query);
            if (foundedItems == null || foundedItems.Rows.Count == 0) return null;
            return GetDocumentSetById(int.Parse(foundedItems.Rows[0]["ID"].ToString()), foundedItems.Rows[0][4].ToString());
        }

        public static office.DocumentSet GetExpedientById(string expedienteId, SPWeb miWeb)
        {
            string camlQuery = string.Format(@"<Where>
                                                  <And>
                                                     <Eq>
                                                        <FieldRef Name='Expediente_Id' />
                                                        <Value Type='Text'>{0}</Value>
                                                     </Eq>
                                                     <Eq>
                                                        <FieldRef Name='ContentType' />
                                                        <Value Type='Text'>{1}</Value>
                                                     </Eq>
                                                  </And>
                                               </Where>", expedienteId, GlobalConstants.CTTN_EXPEDIENTE);
            List<string> viewFields = new List<string>() { "ID", CustomSiteColumns.FIELD_DOCUMENT_EXPEDIENTE_ID };
            List<string> viewListFields = new List<string>() { "Title" };
            SPSiteDataQuery query = QuerySiteHelper.getSiteQueryObject(GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE, viewFields, viewListFields, camlQuery);
            DataTable foundedItems = miWeb.GetSiteData(query);
            if (foundedItems == null || foundedItems.Rows.Count == 0) return null;
            return GetDocumentSetById(int.Parse(foundedItems.Rows[0]["ID"].ToString()), foundedItems.Rows[0][4].ToString(), miWeb);
        }

        public static string GetListNameForExpedientById(string expedienteId)
        {
            string camlQuery = string.Format(@"<Where>
                                                  <And>
                                                     <Eq>
                                                        <FieldRef Name='Expediente_Id' />
                                                        <Value Type='Text'>{0}</Value>
                                                     </Eq>
                                                     <Eq>
                                                        <FieldRef Name='ContentType' />
                                                        <Value Type='Text'>{1}</Value>
                                                     </Eq>
                                                  </And>
                                               </Where>", expedienteId, GlobalConstants.CTTN_EXPEDIENTE);
            List<string> viewFields = new List<string>() { "ID", CustomSiteColumns.FIELD_DOCUMENT_EXPEDIENTE_ID };
            List<string> viewListFields = new List<string>() { "Title" };
            SPSiteDataQuery query = QuerySiteHelper.getSiteQueryObject(GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE, viewFields, viewListFields, camlQuery);
            DataTable foundedItems = SPContext.Current.Web.GetSiteData(query);
            if (foundedItems == null || foundedItems.Rows.Count == 0) return null;
            return foundedItems.Rows[0][4] == null ? string.Empty : foundedItems.Rows[0][4].ToString();
        }

        public static string GetParentExpedientByRadicadoId(string radicadoId)
        {
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='{0}' /><Value Type='Text'>{1}</Value></Eq></Where>", CustomSiteColumns.FIELD_DOCSET_UNIQUE_ID, radicadoId);
            List<string> viewFields = new List<string>() { "ID", CustomSiteColumns.FIELD_DOCUMENT_EXPEDIENTE_ID };
            SPSiteDataQuery query = QuerySiteHelper.getSiteQueryObject(GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE, viewFields, camlQuery);
            DataTable foundedItems = SPContext.Current.Web.GetSiteData(query);
            if (foundedItems == null || foundedItems.Rows.Count == 0) return null;
            return foundedItems.Rows[0][CustomSiteColumns.FIELD_DOCUMENT_EXPEDIENTE_ID] == null ? string.Empty : foundedItems.Rows[0][CustomSiteColumns.FIELD_DOCUMENT_EXPEDIENTE_ID].ToString();
        }

        public static office.DocumentSet GetRadicadoById(string radicadoId, string listName)
        {
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='{0}' /><Value Type='Text'>{1}</Value></Eq></Where>", CustomSiteColumns.FIELD_DOCSET_UNIQUE_ID, radicadoId);
            List<string> viewFields = new List<string>() { "ID" };
            SPSiteDataQuery query = QuerySiteHelper.getSiteQueryObject(GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE, viewFields, camlQuery);
            DataTable foundedItems = SPContext.Current.Web.GetSiteData(query);
            if (foundedItems == null || foundedItems.Rows.Count == 0) return null;
            return GetDocumentSetById(int.Parse(foundedItems.Rows[0]["ID"].ToString()), listName);
        }

        public static office.DocumentSet GetRadicadoById(string radicadoId, Guid listId)
        {
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='{0}' /><Value Type='Text'>{1}</Value></Eq></Where>", CustomSiteColumns.FIELD_DOCSET_UNIQUE_ID, radicadoId);
            List<string> viewFields = new List<string>() { "ID" };
            SPSiteDataQuery query = QuerySiteHelper.getSiteQueryObject(GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE, viewFields, camlQuery);
            DataTable foundedItems = SPContext.Current.Web.GetSiteData(query);
            if (foundedItems == null || foundedItems.Rows.Count == 0) return null;
            return GetDocumentSetById(int.Parse(foundedItems.Rows[0]["ID"].ToString()), listId);
        }

        public static office.DocumentSet GetDocumentSetById(int id, string listName)
        {
            var spFolder = SPContext.Current.Web.Lists[listName].GetItemById(id).Folder;
            return Microsoft.Office.DocumentManagement.DocumentSets.DocumentSet.GetDocumentSet(spFolder);
        }

        public static office.DocumentSet GetDocumentSetById(int id, Guid listId)
        {
            var spFolder = SPContext.Current.Web.Lists[listId].GetItemById(id).Folder;
            return Microsoft.Office.DocumentManagement.DocumentSets.DocumentSet.GetDocumentSet(spFolder);
        }

        public static office.DocumentSet GetDocumentSetById(int id, string listName, SPWeb web)
        {
            SPList targetList = web.Lists.TryGetList(listName);
            if (targetList == null) { throw new Exception(string.Format("La lista {0} no existe", listName)); }
            SPListItem element = targetList.GetItemById(id);
            if (element == null) { throw new Exception( string.Format("No existe un elemento con id {0} en la lista {1}", id, listName)); }
            var spFolder = element.Folder;
            return Microsoft.Office.DocumentManagement.DocumentSets.DocumentSet.GetDocumentSet(spFolder);
        }

        /// <summary>
        /// Return a documentset object giving its id, list and web with elevated privileges
        /// </summary>
        /// <param name="id">document id</param>
        /// <param name="listName">list container</param>
        /// <param name="currrentWeb">current web</param>
        /// <returns></returns>
        //public static office.DocumentSet GetDocumentSetById(int id, string listName, SPWeb currrentWeb)
        //{
        //    var spFolder = currrentWeb.Lists[listName].GetItemById(id).Folder;
        //    return Microsoft.Office.DocumentManagement.DocumentSets.DocumentSet.GetDocumentSet(spFolder);
        //}


        /// <summary>
        /// This method try to get a radicado first in its owner list, second in the owner dependency list
        /// if there cant have the radicado finally a spsitedataquery is executed
        /// </summary>
        /// <param name="radId">Radicado Id</param>
        /// <param name="userName">Radicado Owner</param>
        /// <returns>Documentset Object</returns>
        public static office.DocumentSet TryGetRadicado(string radId, string ownerUserName, SPWeb currentWeb)
        {
            office.DocumentSet foundedItem = null;

            //Trying to get radicado in owner list and department
            string departmentList = PeopleHelper.GetPropertyUserProfile(GlobalConstants.UP_DEPARTMENT, ownerUserName, currentWeb).Trim();
            string ownerListName = ownerUserName.GetUserWithOutDomain().Trim();
            foundedItem = GetRadicadoByIdList(radId, new List<string>() { ownerListName, departmentList }, currentWeb);

            if (foundedItem != null)
                return foundedItem;

            //Trying to get a radicado searching in the whole site
            foundedItem = GetRadicadoByIdQuerySite(radId, currentWeb);

            return foundedItem;
        }

        /// <summary>
        /// Try to get a radicado by its id and listname
        /// </summary>
        /// <param name="radicadoId">radicado Id</param>
        /// <param name="listName">Container list</param>
        /// <returns></returns>
        public static office.DocumentSet GetRadicadoByIdList(string radicadoId, List<string> listsName, SPWeb currentWeb)
        {
            List<string> listIds = ListHelper.getListIds(listsName, currentWeb);
            List<string> viewFields = new List<string>() { "ID" };
            List<string> listFields = new List<string>() { "Title" };
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='{0}' /><Value Type='Text'>{1}</Value></Eq></Where>", CustomSiteColumns.FIELD_DOCSET_UNIQUE_ID, radicadoId);

            SPSiteDataQuery query = QuerySiteHelper.getSiteQueryObject(listIds, viewFields, listFields, camlQuery);
            DataTable foundedItems = currentWeb.GetSiteData(query);

            if (foundedItems != null && foundedItems.Rows.Count > 0)
                return GetDocumentSetById(int.Parse(foundedItems.Rows[0]["ID"].ToString()), foundedItems.Rows[0][3].ToString(), currentWeb);
            return null;
        }

        /// <summary>
        /// Get a radicado document set by querying in all site
        /// </summary>
        /// <param name="radicadoId">Radicado Id</param>
        /// <returns>Documentset Object for Radicado</returns>
        public static office.DocumentSet GetRadicadoByIdQuerySite(string radicadoId, SPWeb currentWeb)
        {
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='{0}' /><Value Type='Text'>{1}</Value></Eq></Where>", CustomSiteColumns.FIELD_DOCSET_UNIQUE_ID, radicadoId);
            List<string> viewFields = new List<string>() { "ID" };
            List<string> listFields = new List<string>() { "Title" };
            SPSiteDataQuery query = QuerySiteHelper.getSiteQueryObject(GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE, viewFields, listFields, camlQuery);
            DataTable foundedItems = currentWeb.GetSiteData(query);
            if (foundedItems == null || foundedItems.Rows.Count == 0) return null;
            return GetDocumentSetById(int.Parse(foundedItems.Rows[0]["ID"].ToString()), foundedItems.Rows[0][3].ToString(), currentWeb);
        }


        public static Radicado GetRadicadoById(string radId)
        {
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='{0}' /><Value Type='Text'>{1}</Value></Eq></Where>", CustomSiteColumns.FIELD_DOCSET_UNIQUE_ID, radId);
            SPSiteDataQuery query = new SPSiteDataQuery();
            query.Lists = string.Format("<Lists ServerTemplate='{0}' />", GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE);
            query.ViewFields = "<ListProperty Name='Title' />";
            query.Webs = "<Webs Scope='SiteCollection' />";
            query.Query = camlQuery;
            DataTable foundedItems = SPContext.Current.Web.GetSiteData(query);
            if (foundedItems == null || foundedItems.Rows.Count == 0) return null;
            List<Radicado> radicados = new List<Radicado>();
            string currentListName = foundedItems.Rows[0][3].ToString();
            using (var dc = new DCDocumentCenterDataContext(SPContext.Current.Web.Url))
            {
                var radicado = (from rad in dc.GetList<Radicado>(currentListName).ScopeToFolder("", true)
                                where rad.NoRadicado == radId
                                select rad).FirstOrDefault();
                return radicado;
            }
        }

        public static List<Radicado> GetRadicadoByIdCurrentLibrary(List<string> radids)
        {

            List<Radicado> radicados = new List<Radicado>();

            using (var dc = new DCDocumentCenterDataContext(SPContext.Current.Web.Url))
            {
                foreach (var id in radids)
                {
                    var radicado = (from rad in dc.GetList<Radicado>(UserLibraryHelper.CurrentUserLibraryName).ScopeToFolder("", true)
                                    where rad.NoRadicado == id
                                    select rad).FirstOrDefault();
                    radicados.Add(radicado);
                }
                return radicados;
            }
        }

        public static string GetExpedienteSubjectById(string expedienteId)
        {
            List<string> viewFields = new List<string>() { CustomSiteColumns.FIELD_EXPEDIENTE_SUBJECT };
            string queryFilter = getExpedienteQuery(expedienteId);
            SPSiteDataQuery siteQuery = QuerySiteHelper.getSiteQueryObject(GlobalConstants.GLOBAL_USER_LIBRARY_TEMPLATE, viewFields, queryFilter);
            var foundedItems = SPContext.Current.Web.GetSiteData(siteQuery);
            return foundedItems != null && foundedItems.Rows.Count > 0 ? foundedItems.Rows[0][CustomSiteColumns.FIELD_EXPEDIENTE_SUBJECT].ToString() : string.Empty;
        }

        public static string GetUserSeccionEncharged(string target)
        {
            using (var dc = new DAL.RecordCenter.DataContext(SPContext.Current.Web.Url))
            {
                var encharged = (from dept in dc.Secciones
                                 where dept.Id.ToString() == target
                                 select dept).FirstOrDefault();
                if (encharged == null)
                    throw new Exception(ExceptionConstants.ERROR_DEPARTMENT_NO_ENCHARGED);
                return PeopleHelper.GetUserById(encharged.UsuarioEncargadoCorrespondenciaId.Value).LoginName;
            }
        }

        public static string GetUserSeccionEnchargedIfExists(string target)
        {
            using (var dc = new DAL.RecordCenter.DataContext(SPContext.Current.Web.Url))
            {
                var encharged = (from dept in dc.Secciones
                                 where dept.Id.ToString() == target
                                 select dept).FirstOrDefault();
                if (encharged != null)
                    return PeopleHelper.GetUserById(encharged.UsuarioEncargadoCorrespondenciaId.Value).LoginName;
                else
                    return null;
            }
        }


        public static string GetUserSeccionName(string target)
        {
            using (var dc = new DAL.RecordCenter.DataContext(SPContext.Current.Web.Url))
            {
                var encharged = (from dept in dc.Secciones
                                 where dept.Id.ToString() == target
                                 select dept).FirstOrDefault();
                if (encharged == null)
                    throw new Exception(ExceptionConstants.ERROR_DEPARTMENT_NO_ENCHARGED);
                return encharged.Title;
            }
        }


        private static string getExpedienteQuery(string idExpediente)
        {
            List<string> fields = new List<string>() { CustomSiteColumns.FIELD_EXPEDIENTE_ID, UserLibraryHelper.ContentTypeFieldName };
            List<string> values = new List<string>() { idExpediente, GlobalConstants.CTTN_EXPEDIENTE };
            List<string> types = new List<string>() { "Text", "Text" };
            List<string> operatorTypes = new List<string>() { "Eq", "Eq" };
            string _cadena = string.Empty;
            return support.CamlHelper.CreateCAMLQuery(values.ToArray(), fields.ToArray(), types.ToArray(), operatorTypes.ToArray(), ref _cadena);
        }

    }
}
